create table CITY
(
  id     NUMBER not null,
  name   NVARCHAR2(100),
  status NUMBER default 0
);

create table MANAGER
(
  id               NUMBER not null,
  name             NVARCHAR2(100),
  birthday         DATE,
  gender           NUMBER,
  alias            NVARCHAR2(100),
  password         NVARCHAR2(500),
  telephone        NVARCHAR2(30),
  email            NVARCHAR2(100),
  address          NVARCHAR2(300),
  createtime       TIMESTAMP(6),
  createuserid     NUMBER,
  lastchangetime   TIMESTAMP(6),
  lastchangeuserid NUMBER,
  worknum          NVARCHAR2(50),
  status           NUMBER default 0,
  roleid           NUMBER,
  appid            NVARCHAR2(100),
  cityid           NUMBER
);

create table MENU
(
  id       NUMBER not null,
  parentid NUMBER default 0,
  name     NVARCHAR2(100),
  status   NUMBER default 0,
  sort     NUMBER,
  url      NVARCHAR2(255)
);

create table ROLE
(
  id          NUMBER not null,
  name        NVARCHAR2(100),
  description NVARCHAR2(300),
  status      NUMBER default 0,
  rolelevel   NUMBER default 0
);

create table ROLE_MENU
(
  roleid NUMBER not null,
  menuid NUMBER not null
);

create table ROP_APP_SECRET
(
  appid              NVARCHAR2(100) not null,
  appsecret          NVARCHAR2(255) not null,
  description        NVARCHAR2(255),
  status             NUMBER default 0,
  createdatetime     TIMESTAMP(6),
  lastmodifydatetime TIMESTAMP(6),
  id                 NUMBER not null,
  cityid             NUMBER not null,
  oldcount           NUMBER default 0
);

create table ROP_SESSION
(
  id        NUMBER not null,
  managerid NUMBER not null,
  roleid    NUMBER not null,
  sessionid NVARCHAR2(100) not null,
  appid     NVARCHAR2(100) not null,
  clientip  NVARCHAR2(100)
);

create table WECHAT_ACCESS_TOKEN
(
  accesstoken NVARCHAR2(300),
  appid       NVARCHAR2(100),
  updatetime  TIMESTAMP(6)
);

create table WECHAT_ARTICLE
(
  id                NUMBER not null,
  parentid          NUMBER default 0,
  title             NVARCHAR2(255),
  description       NVARCHAR2(255),
  picurl            NVARCHAR2(255),
  url               NVARCHAR2(255),
  status            NUMBER default 0,
  createtime        TIMESTAMP(6),
  createuserid      NUMBER,
  lastchangedtime   TIMESTAMP(6),
  lastchangeduserid NUMBER,
  appid             NVARCHAR2(100),
  sort              NUMBER default 0
);

create table WECHAT_MENU
(
  id       NUMBER not null,
  name     NVARCHAR2(100),
  type     NVARCHAR2(20),
  key      NVARCHAR2(255),
  url      NVARCHAR2(255),
  parentid NUMBER default 0,
  sort     NUMBER,
  status   NUMBER default 0,
  appid    NVARCHAR2(100)
);

create table WECHAT_REQUEST
(
  id           NUMBER not null,
  userid       NUMBER not null,
  respid       NUMBER,
  msgtype      NVARCHAR2(20),
  content      NVARCHAR2(255),
  picurl       NVARCHAR2(255),
  mediaid      NVARCHAR2(100),
  format       NVARCHAR2(20),
  thumbmediaid NVARCHAR2(100),
  location_x   NUMBER,
  location_y   NUMBER,
  scale        NUMBER,
  label        NVARCHAR2(255),
  title        NVARCHAR2(255),
  description  NVARCHAR2(255),
  url          NVARCHAR2(255),
  msgid        NVARCHAR2(100),
  createtime   TIMESTAMP(6),
  event        NVARCHAR2(100),
  eventkey     NVARCHAR2(255),
  respmsg      NVARCHAR2(2000)
);

create table WECHAT_RESP
(
  id                NUMBER not null,
  msgtype           NVARCHAR2(20),
  content           NVARCHAR2(1000),
  mediaid           NVARCHAR2(100),
  title             NVARCHAR2(1000),
  description       NVARCHAR2(255),
  musicurl          NVARCHAR2(255),
  hqmusicurl        NVARCHAR2(255),
  thumbmediaid      NVARCHAR2(255),
  status            NUMBER default 0,
  createtime        TIMESTAMP(6),
  createuserid      NUMBER,
  lastchangedtime   TIMESTAMP(6),
  lastchangeduserid NUMBER,
  appid             NVARCHAR2(100),
  keyword           NVARCHAR2(255),
  priority          NUMBER default 0,
  keyvalue          NVARCHAR2(100),
  adddefault        NUMBER default 0,
  shorttitle        NVARCHAR2(255),
  ismenu            NUMBER default 0
);

create table WECHAT_RESP_ARTICLE
(
  respid    NUMBER not null,
  articleid NUMBER not null
);

create table WECHAT_USER
(
  id             NUMBER not null,
  appid          NVARCHAR2(100) not null,
  subscribe      NUMBER,
  openid         NVARCHAR2(50),
  nickname       NVARCHAR2(100),
  sex            NUMBER,
  language       NVARCHAR2(30),
  city           NVARCHAR2(30),
  province       NVARCHAR2(30),
  country        NVARCHAR2(30),
  headimgurl     NVARCHAR2(255),
  subscribe_time NUMBER,
  unionid        NVARCHAR2(50),
  telephone      NVARCHAR2(30),
  status         NUMBER default 0
);

create sequence SEQ_CITY
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

create sequence SEQ_MANAGER
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

create sequence SEQ_MENU
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

create sequence SEQ_ROLE
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

create sequence SEQ_ROP_APP_SECRET
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

create sequence SEQ_ROP_SESSION
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

create sequence SEQ_WECHAT_ARTICLE
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

create sequence SEQ_WECHAT_KEYWORD
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

create sequence SEQ_WECHAT_MENU
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

create sequence SEQ_WECHAT_REQUEST
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

create sequence SEQ_WECHAT_RESP
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

create sequence SEQ_WECHAT_USER
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

create table wechat_user_analyse
(
  id            number not null,
  thedate       timestamp,
  addcount      number default 0,
  cancelcount   number default 0,
  focuscount    number default 0,
  allfocuscount number default 0,
  appid         NVARCHAR2(100) not null
);

create sequence SEQ_WECHAT_USER_ANALYSE
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

create table OTHERTEL
(
  nosegment NUMBER,
  tel       NVARCHAR2(30)
);

create table TEL
(
  nosegment NUMBER,
  area      NVARCHAR2(50),
  numtype   NVARCHAR2(2),
  areanum   NVARCHAR2(4)
);

create table PLAYGAME
(
  id         NUMBER not null,
  appid      NVARCHAR2(100),
  openid     NVARCHAR2(100),
  gamename   NVARCHAR2(100),
  tel        NVARCHAR2(30),
  result     NVARCHAR2(1000),
  prize      NUMBER,
  createtime NUMBER,
  finishtime NUMBER
);

create sequence SEQ_PLAY_GAME
minvalue 1
maxvalue 999999999999999999999999999
start with 41
increment by 1
cache 20;


alter table wechat_user_analyse add(oldcount NUMBER default 0);
alter table wechat_user_analyse add(oldcancelcount NUMBER default 0);
alter table wechat_user_analyse add(newcancelcount NUMBER default 0);
alter table wechat_user_analyse add(newfocuscount NUMBER default 0);

alter table wechat_user add(isolduser NUMBER default 0);
alter table wechat_user add(memberstatus NUMBER default 0);

create table PLAYGAMERANKING
(
  appid      NVARCHAR2(100),
  openid     NVARCHAR2(100),
  gamename   NVARCHAR2(100),
  tel        NVARCHAR2(30),
  result     NVARCHAR2(1000),
  prize      NUMBER,
  createtime NUMBER,
  finishtime NUMBER
);

---------------------------------------------------
-- 初始化数据
---------------------------------------------------
insert into city values (seq_city.nextval, '内蒙古', 0);
insert into rop_app_secret values ('woshimorendeappid123','4ff95c132f05d3107943ee1ad17ec453','所有微信号都能看到的信息',0,null,null,SEQ_ROP_APP_SECRET.nextval,1,0);
insert into role values (seq_role.nextval, '总管理员', '管理所有微信公用信息和接入微信号的个性化信息', 0, 0);
insert into role values (seq_role.nextval, '盟市管理员', '管理盟市微信的权限', 0, 1);
insert into role values (seq_role.nextval, '盟市员工', '普通的使用权限', 0, 2);

-----password: 123456
insert into manager(id, worknum, name, password, roleid, appid, alias, telephone, email, status)
    values (seq_manager.nextval, 'admin01', 'admin', '7C4A8D09CA3762AF61E59520943DC26494F8941B', 1, 'woshimorendeappid123', '高献涛', '18047131202', 'gaoxiantao@wxtc-tech.com', 0);

insert into menu values (seq_menu.nextval, 0, '后台系统设置', 0, 1, '');
insert into menu values (seq_menu.nextval, 0, '微信系统设置', 0, 2, '');

insert into menu values (seq_menu.nextval, 1, '城市管理', 0, 10, 'cityList.html');
insert into menu values (seq_menu.nextval, 1, '微信号管理', 0, 11, 'ropAppSecretList.html');
insert into menu values (seq_menu.nextval, 1, '角色权限管理', 0, 12, 'roleList.html');
insert into menu values (seq_menu.nextval, 1, '导航菜单管理', 0, 13, 'menuList.html');
insert into menu values (seq_menu.nextval, 1, '管理员管理', 0, 14, 'managerList.html');


insert into menu values (seq_menu.nextval, 2, '菜单管理', 0, 10, 'wechatMenuList.html');
insert into menu values (seq_menu.nextval, 2, '欢迎语管理', 0, 11, 'wechatWelcomeList.html');
insert into menu values (seq_menu.nextval, 2, '默认应答管理', 0, 12, 'wechatDefaultList.html');
insert into menu values (seq_menu.nextval, 2, '自动应答管理', 0, 13, 'wechatRespList.html');
insert into menu values (seq_menu.nextval, 2, '图文管理', 0, 14, 'wechatArticleList.html');

insert into role_menu values (1, 3);
insert into role_menu values (1, 4);
insert into role_menu values (1, 5);
insert into role_menu values (1, 6);
insert into role_menu values (1, 7);
insert into role_menu values (1, 8);
insert into role_menu values (1, 9);
insert into role_menu values (1, 10);
insert into role_menu values (1, 11);
insert into role_menu values (1, 12);

insert into role_menu values (2, 7);
insert into role_menu values (2, 8);
insert into role_menu values (2, 9);
insert into role_menu values (2, 10);
insert into role_menu values (2, 11);
insert into role_menu values (2, 12);

insert into role_menu values (3, 8);
insert into role_menu values (3, 9);
insert into role_menu values (3, 10);
insert into role_menu values (3, 11);
insert into role_menu values (3, 12);

insert into menu values (seq_menu.nextval, 0, '微信统计分析', 0, 3, '');
insert into menu values (seq_menu.nextval, 13, '请求消息日志', 0, 10, 'wechatRequestList.html');
insert into menu values (seq_menu.nextval, 13, '用户关注日志', 0, 11, 'wechatUserList.html');

insert into role_menu values (1, 14);
insert into role_menu values (1, 15);

insert into role_menu values (2, 14);
insert into role_menu values (2, 15);

insert into role_menu values (3, 14);
insert into role_menu values (3, 15);

insert into menu values (seq_menu.nextval, 13, '微信用户分析', 0, 12, 'wechatUserAnalyse.html');
insert into role_menu values (1, 16);
insert into role_menu values (2, 16);
insert into role_menu values (3, 16);

insert into menu values (seq_menu.nextval, 0, '手机号码维护', 0, 4, '');
insert into menu values (seq_menu.nextval, 17, '未识别号码', 0, 10, 'otherTelList.html');
insert into role_menu values (1, 18);

insert into menu values (seq_menu.nextval, 0, '图片审核', 0, 5, '');
insert into menu values (seq_menu.nextval, 19, '分享有礼', 0, 10, 'playGameList.html');
insert into role_menu values (1, 20);